package com.springapp.dao.dao.impl;

import com.springapp.dao.dao.IPaletteEnStockDAO;
import com.springapp.model.Palette;
import org.json.JSONException;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Created by nicol_000 on 27/08/14.
 */
@Repository
public class PaletteEnStockDAO implements IPaletteEnStockDAO {

    @Qualifier("dataSource") //Fait référence au datasource dans le mvc-dispatcher.xml
    @Autowired
    private DataSource dataSource;


    public Palette getPalette(int _idPalette){
        Palette pal = new Palette();
        Boolean bReq = true;
        String sql="" +
                "SELECT *\n" +
                "FROM Palette Pal\n" +
                "WHERE Pal.IDPalette='"+_idPalette+"'\n";

        Connection conn = null;
        try {
            conn=dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet result=ps.executeQuery();
            if(result.next()){
                pal.setbEstHeterogène(result.getBoolean("EstHeterogene"));
                pal.setiIDPalette(result.getInt("IDPalette"));
                pal.setsLibelle(result.getString("libelle"));
                pal.seteIDEmplacement(result.getInt("IDEmplacement"));
                pal.seteIDTypePalette(result.getInt("IDTypePalette"));
                pal.seteIDIDEtatOperation(result.getInt("IDEtatOperation"));
            }else{bReq=false;}
            result.close();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if(bReq){
            return pal;
        }else{
            return null;
        }
    }

    public JSONObject getQteArtFou(int _iIDPalette){
        String sql="" +
                "SELECT QteArticle, IDArtFou FROM Pal_ArtFou WHERE IDPalette='"+_iIDPalette+"'";
        System.out.println(sql);
        Connection conn = null;
        JSONObject jsoRet= new JSONObject();
        try {
            conn=dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs =ps.executeQuery();
            if(rs.next()){
                try {
                    jsoRet.put("QteArticle", rs.getInt("qteArticle"));
                    jsoRet.put("IdArtFou",rs.getInt("IDArtFou"));
                } catch (JSONException e) {
                    e.printStackTrace();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return jsoRet;
    }

    public String update(Palette pal){
        String sRet = "";
        String sql="" +
                "UPDATE Palette SET libelle='"+pal.getsLibelle()+"'\n," +
                "IDTypePalette='"+pal.geteIDTypePalette()+"'\n," +
                "IDEtatOperation='"+pal.geteIDIDEtatOperation()+"'\n," +
                "IDEmplacement='"+pal.geteIDEmplacement()+"'\n," +
                "EstHeterogene='"+pal.isbEstHeterogène()+"'\n " +
                "WHERE IDPalette='"+pal.getiIDPalette()+"'";
        System.out.println(sql);
        Connection conn = null;
        try {
            conn=dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            int iRet=ps.executeUpdate();
            ps.close();
            if(iRet==0){
                sRet="Echec de la modification de la palette";
            }else{
                sRet="PaletteMise à jour";
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sRet;

    }
}
